package org.yuangu.privacyfinance.domain.access;

import org.joda.time.LocalDate;
import org.yuangu.privacyfinance.activities.helper.CommunicantAsyncTask;
import org.yuangu.privacyfinance.activities.helper.TaskListener;
import org.yuangu.privacyfinance.domain.model.Transaction;

import java.util.List;

import androidx.lifecycle.LiveData;
import androidx.room.Dao;
import androidx.room.Query;

/**
 * 交易数据库表访问类
 */
@Dao
public abstract class TransactionDao extends AbstractDao<Transaction> {
    /**
     * 通过id获取交易数据
     */
    @Override
    @Query("SELECT * FROM Tranzaction WHERE id=:id")
    public abstract LiveData<Transaction> get(long id);

    /**
     * 获取所有交易数据列表
     */
    @Override
    @Query("SELECT * FROM Tranzaction ORDER BY date DESC")
    public abstract LiveData<List<Transaction>> getAll();

    /**
     * 根据账号id获取指定账号按日期降序的交易数据列表
     * @param accountId 账户Id
     * @return
     */
    @Query("SELECT * FROM Tranzaction WHERE accountId=:accountId ORDER BY date DESC")
    public abstract LiveData<List<Transaction>> getForAccount(long accountId);

    /**
     * 根据账户Id获取指定日期（含）之后的按日期降序的交易数据列表
     * @param accountId 账户Id
     * @param date 日期
     * @return 交易数据列表
     */
    @Query("SELECT * FROM Tranzaction WHERE accountId=:accountId AND date>=:date ORDER BY date DESC")
    public abstract LiveData<List<Transaction>> getForAccountFrom(long accountId, String date);

    /**
     * 根据账户Id获取指定日期（不含）之前的按日期降序的交易数据列表
     * @param accountId 账户Id
     * @param date 日期
     * @return 交易数据列表
     */
    @Query("SELECT * FROM Tranzaction WHERE accountId=:accountId AND date<:date ORDER BY date DESC")
    public abstract LiveData<List<Transaction>> getForAccountBefore(long accountId, String date);

    /**
     * 根据分类Id获取按日期降序的交易数据列表
     * @param categoryId 分类Id
     * @return 交易数据列表
     */
    @Query("SELECT * FROM Tranzaction WHERE categoryId=:categoryId ORDER BY date DESC")
    public abstract LiveData<List<Transaction>> getForCategory(long categoryId);

    /**
     * 根据账户Id和分类Id获取对应账户和分类的按日期降序的交易数据列表
     * @param accountId 账户Id
     * @param categoryId 分类Id
     * @return 交易数据列表
     */
    @Query("SELECT * FROM Tranzaction WHERE accountId=:accountId AND categoryId=:categoryId ORDER BY date DESC")
    public abstract LiveData<List<Transaction>> getForAccountAndCategory(long accountId, long categoryId);

    @Query("SELECT * FROM Tranzaction WHERE repeatingId=:repeatingId ORDER BY date DESC")
    public abstract LiveData<List<Transaction>> getByRepeatingId(long repeatingId);

    /**
     * 获取对应分类的收入数据
     * @param categoryId 分类Id
     * @return 分类的收入
     */
    @Query("SELECT SUM(amount) FROM Tranzaction WHERE categoryId=:categoryId AND amount>0")
    public abstract LiveData<Long> sumIncomeForCategory(long categoryId);

    /**
     * 获取对应分类的支出数据
     * @param categoryId 分类Id
     * @return 分类的支出
     */
    @Query("SELECT SUM(amount) FROM Tranzaction WHERE categoryId=:categoryId AND amount<0")
    public abstract LiveData<Long> sumExpensesForCategory(long categoryId);

    /**
     * 获取指定账户Id的净收入
     * @param accountId 账户Id
     * @return 账户的净收入
     */
    @Query("SELECT SUM(amount) FROM Tranzaction WHERE accountId=:accountId")
    public abstract LiveData<Long> sumForAccount(long accountId);

    /**
     * 获取对应账户Id的指定日期（含）之后的净收入
     * @param accountId 账户Id
     * @param date 日期
     * @return 账户的净收入
     */
    @Query("SELECT SUM(amount) FROM Tranzaction WHERE accountId=:accountId AND date>=:date")
    public abstract LiveData<Long> sumForAccountFrom(long accountId, String date);

    /**
     * 获取对应账户Id的指定日期（不含）之前的净收入
     * @param accountId 账户Id
     * @param date 日期
     * @return 账户的净收入
     */
    @Query("SELECT SUM(amount) FROM Tranzaction WHERE accountId=:accountId AND date<:date")
    public abstract LiveData<Long> sumForAccountBefore(long accountId, String date);

    /**
     * 获取指定分类Id的净收入
     * @param categoryId 分类Id
     * @return 分类的净收入
     */
    @Query("SELECT SUM(amount) FROM Tranzaction WHERE categoryId=:categoryId")
    public abstract LiveData<Long> sumForCategory(long categoryId);

    /**
     * 获取对应账户Id和分类Id的净收入
     * @param accountId 账户Id
     * @param categoryId 分类Id
     * @return 对应账户和分类的净收入
     */
    @Query("SELECT SUM(amount) FROM Tranzaction WHERE accountId=:accountId AND categoryId=:categoryId")
    public abstract LiveData<Long> sumForAccountAndCategory(long accountId, long categoryId);

    /**
     * 获取对应分类Id的指定日期（含）之后的净收入
     * @param categoryId 分类Id
     * @param date 指定日期（含）
     * @return 对应分类指定日期后净收入
     */
    @Query("SELECT SUM(amount) FROM Tranzaction WHERE categoryId=:categoryId AND date>=:date")
    public abstract LiveData<Long> sumForCategoryFrom(long categoryId, String date);

    /**
     * 获取对应分类Id的指定日期（含）之后和指定日期（不含）之后的净收入
     * @param categoryId  分类Id
     * @param from 指定日期（含）下界
     * @param before 指定日期（不含）上界
     * @return 对应分类指定日期之间净收入
     */
    @Query("SELECT SUM(amount) FROM Tranzaction WHERE categoryId=:categoryId AND date>=:from and date<:before")
    public abstract LiveData<Long> sumForCategoryFromBefore(long categoryId, String from, String before);

    /**
     * 获取对应分类Id的指定日期（含）之后和指定日期（不含）之后的收入
     * @param categoryId  分类Id
     * @param from 指定日期（含）下界
     * @param before 指定日期（不含）上界
     * @return 对应分类指定日期之间收入
     */
    @Query("SELECT SUM(amount) FROM Tranzaction WHERE categoryId=:categoryId AND amount > 0 AND date>=:from and date<:before")
    public abstract LiveData<Long> sumIncomeForCategoryFromBefore(long categoryId, String from, String before);

    /**
     * 获取对应分类Id的指定日期（含）之后和指定日期（不含）之后的支出
     * @param categoryId  分类Id
     * @param from 指定日期（含）下界
     * @param before 指定日期（不含）上界
     * @return 对应分类指定日期之间支出
     */
    @Query("SELECT SUM(amount) FROM Tranzaction WHERE categoryId=:categoryId AND amount < 0 AND date>=:from and date<:before")
    public abstract LiveData<Long> sumExpensesForCategoryFromBefore(long categoryId, String from, String before);

    /**
     * 当月对应分类Id的净收入
     * @param categoryId 分类Id
     * @return 当月对应分类的净收入
     */
    public LiveData<Long> sumForCategoryThisMonth(long categoryId) {
        return sumForCategoryFromBefore(categoryId, LocalDate.now().withDayOfMonth(1).toString(), LocalDate.now().withDayOfMonth(1).plusMonths(1).toString());
    }

    /**
     * 当月分类Id的收入
     * @param categoryId 分类Id
     * @return 当月对应分类的收入
     */
    public LiveData<Long> sumIncomeForCategoryThisMonth(long categoryId) {
        return sumIncomeForCategoryFromBefore(categoryId, LocalDate.now().withDayOfMonth(1).toString(), LocalDate.now().withDayOfMonth(1).plusMonths(1).toString());
    }

    /**
     * 当月分类Id的支出
     * @param categoryId 分类Id
     * @return 当月对应分类的支出
     */
    public LiveData<Long> sumExpensesForCategoryThisMonth(long categoryId) {
        return sumExpensesForCategoryFromBefore(categoryId, LocalDate.now().withDayOfMonth(1).toString(), LocalDate.now().withDayOfMonth(1).plusMonths(1).toString());
    }

    @Query("SELECT * FROM Tranzaction WHERE accountId=:accountId AND name=:name AND date>=:from AND date<:before ORDER BY date DESC LIMIT 1")
    public abstract LiveData<Transaction> getLatestByNameAndAccountFromBefore(long accountId, String name, String from, String before);

    @Query("SELECT * FROM Tranzaction WHERE accountId=:accountId AND name=:name AND date>=:from AND date<:before ORDER BY date DESC LIMIT 1")
    public abstract Transaction getLatestByNameAndAccountFromBeforeSync(long accountId, String name, String from, String before);

    public LiveData<Transaction> getLatestByNameForAccountLastMonth(Long accountId, String name) {
        return getLatestByNameAndAccountFromBefore(accountId, name, LocalDate.now().minusMonths(1).withDayOfMonth(1).toString(), LocalDate.now().withDayOfMonth(1).toString());
    }

    public CommunicantAsyncTask<?, Transaction> getLatestByNameForAccountLastMonthAsync(final Long accountId, final String name, TaskListener listener) {
        return listenAndExec(new CommunicantAsyncTask<Void, Transaction>() {
            @Override
            protected Transaction doInBackground(Void... voids) {
                return getLatestByNameAndAccountFromBeforeSync(accountId, name, LocalDate.now().minusMonths(1).withDayOfMonth(1).toString(), LocalDate.now().withDayOfMonth(1).toString());
            }
        }, listener);
    }

}
